set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 200;
-- **************************************************************************
--    Project Name:   转邮监控明细
--    Job Name:       jms_dm.dm_tab_transfer_mail_waybill_detail_dt
--    Description :   转邮监控汇总
--    Author :        申家明
--    date：          2022/08/05
-- **************************************************************************
--    Relation ：
--    jms_dm.dm_tab_transfer_mail_waybill_detail_dt << [
--       jms_dwd.dwd_tab_barscan_transfer_mail_base_dt,
--       jms_dwd.dwd_tab_barscan_deliver_base_dt,
--       jms_dwd.dwd_tab_barscan_arrival_base_dt,
--        jms_dwd.dwd_tab_barscan_sign_base_dt,
--        jms_dim.dim_network_whole_massage
--    ]
-- **************************************************************************
with transfer_mail as (
    select bill_code      -- 运单
         , scan_site_code --扫描站点code
         , scan_site_id   --扫描站点id
         , input_site     --录入部门
         , scan_time      --扫描时间
         , scan_user_code --扫描员
         , scan_user      --扫描员名称
         , substr(scan_time, 1, 10) as date_time
    from (
             select bill_code      -- 运单
                  , scan_site_code --扫描站点code
                  , scan_site_id   --扫描站点id
                  , input_site     --录入部门
                  , scan_time      --扫描时间
                  , scan_user_code --扫描员
                  , scan_user      --扫描员名称
                  , row_number() over (partition by bill_code order by scan_time desc ) as rn
             from jms_dwd.dwd_tab_barscan_transfer_mail_base_dt
             where dt between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
             and substr(scan_time, 1, 10) between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
         ) a
    where rn = 1
),
     deliver_base as (
         select waybill_no     --运单编号
              , scan_site_code --扫描网点编号
              , scan_site      --扫描网点
              , scan_time      --扫描时间
              , is_protocol    --是否自动产生
              , protocol_flag  --1代表菜鸟打标,0代表菜鸟未打标
         from (
                  select waybill_no                                                                            --运单编号
                       , scan_site_code                                                                        --扫描网点编号
                       , scan_site                                                                             --扫描网点
                       , scan_time                                                                             --扫描时间
                       , is_protocol                                                                           --是否自动产生
                       , if(is_protocol = 1, 1, 0)                                            as protocol_flag --1代表菜鸟打标,0代表菜鸟未打标
                       , row_number() over (partition by waybill_no order by scan_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_deliver_base_dt
                  where dt between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
                    and is_protocol = 1 --做了打标操作的
              ) a
         where rn = 1
     ),
     arrival_base as (
         select waybill_no     --运单编号
              , scan_site_code --扫描网点编号
              , scan_site      --扫描网点
              , scan_time      --扫描时间
              , source         --来源
              , source_flag    --1代表菜鸟/邮政,0代表非菜鸟/邮政
         from (
                  select waybill_no     --运单编号
                       , scan_site_code --扫描网点编号
                       , scan_site      --扫描网点
                       , scan_time      --扫描时间
                       , source         --来源
                       , if(source in ('菜鸟', '邮政'), 1, 0)                                     as source_flag
                       , row_number() over (partition by waybill_no order by scan_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_arrival_base_dt
                  where dt between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
                    and source in ('菜鸟', '邮政')
              ) a
         where rn = 1
     ),
     sign_base as (
         select waybill_no     --运单编号
              , scan_site_code --扫描网点编号
              , scan_site      --扫描网点
              , scan_time      --扫描时间
              , source         --来源
              , source_flag    --1代表菜鸟/邮政,0代表非菜鸟/邮政
         from (
                  select waybill_no                                                                               --运单编号
                       , signed_site_code                                                       as scan_site_code --扫描网点编号
                       , signed_site                                                            as scan_site      --扫描网点
                       , signed_time                                                            as scan_time      --扫描时间
                       , source                                                                                   --来源
                       , if(source in ('菜鸟', '邮政'), 1, 0)                                       as source_flag
                       , row_number() over (partition by waybill_no order by signed_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_sign_base_dt
                  where dt between date_add('{{ execution_date | cst_ds }}', -6) and '{{ execution_date | cst_ds }}'
              )
         where rn = 1
     ),
     network_whole as (
         select id
              , code
              , name
              , agent_code
              , agent_name
              , fran_code
              , fran_name
              , network_type
         from jms_dim.dim_network_whole_massage
     )
insert
overwrite
table
jms_dm.dm_tab_transfer_mail_waybill_detail_dt
partition
(
dt
)
select tra.bill_code                                                                  as bill_code          -- 运单
     , tra.scan_site_code                                                             as scan_site_code     --扫描站点code
     , tra.input_site                                                                 as input_site         --录入部门
     , net.agent_code                                                                 as agent_code         --代理区code
     , net.agent_name                                                                 as agent_name         --代理区名称
     , tra.scan_time                                                                  as scan_time          --扫描时间
     , tra.scan_user_code                                                             as scan_user_code     --扫描员
     , tra.scan_user                                                                  as scan_user          --扫描员名称
     , arr.scan_time                                                                  as arr_scan_time      --邮政到件时间
     , sig.scan_time                                                                  as sig_scan_time      --邮政签收时间
     , sig.scan_site_code                                                             as sig_scan_site_code --签收网点
     , sig.scan_site                                                                  as scan_site          --签收网点
     , case when del.waybill_no is not null then 1 else 0 end                         as del_falg           --1打标,0未达标 关联上说明打标,关联不上的未打标
     , case when arr.waybill_no is not null then 1 else 0 end                         as arr_falg           --1邮政到件,0未邮政到件 关联上说明邮政/菜鸟,关联不上的未非邮政的
     , case when sig.waybill_no is not null then 1 else 0 end                         as sig_falg           --1 签收,0未 签收 关联上说明签收 ,关联不上的未签收
     , case
           when sig.waybill_no is not null and sig.source_flag = 1 then 1
           else 0 end                                                                 as cnyz_sig_falg      --1邮政签收,0未邮政签收 关联上说明邮政/菜鸟,关联不上的未非邮政的
     , tra.date_time                                                                  as date_time          --日期
     , ''                                                                             as remark1
     , ''                                                                             as remark2
     , tra.date_time                                                                  as dt
from transfer_mail tra
         left join deliver_base del
                   on tra.bill_code = del.waybill_no
         left join arrival_base arr
                   on tra.bill_code = arr.waybill_no
         left join sign_base sig
                   on tra.bill_code = sig.waybill_no
         left join network_whole net
                   on tra.scan_site_code = net.code

 distribute by dt;